package com.oocl.shopping.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import com.oocl.shopping.dao.OrderDao;
import com.oocl.shopping.dto.PayBooksDTO;
import com.oocl.shopping.repository.OrdersDB;
import com.oocl.shopping.util.DBUtil;

public class OrderDaoImpl implements OrderDao{

	@Override
	public boolean addOneOrder(String bookId, String custId) {
		String sql = "insert into orders(id, b_id, c_id, total, buy_date) values(?, ?, ?, ?, ?)";
		Connection con=null;
		PreparedStatement pst = null;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, UUID.randomUUID().toString());
			pst.setString(2, bookId);
			pst.setString(3, custId);
			pst.setInt(4, 1);
			pst.setDate(5, new java.sql.Date(new Date().getTime()));
			pst.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, pst, null);
		}
		return false;
	}

	@Override
	public boolean delOneOrder(String orderId, String custId) {
		String sql = "delete from orders where id = ? and c_id = ?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, orderId);
			pst.setString(2, custId);
			rs = pst.executeQuery();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(null, pst, rs);
		}
		return false;
	}

	@Override
	public boolean delAllOrder(String custId) {
		String sql = "delete from orders where c_id = ?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, custId);
			rs = pst.executeQuery();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(null, pst, rs);
		}
		return false;
	}

	@Override
	public List<PayBooksDTO> getOrders(String custId) {
		String sql = "select * from "
				+ "( select b.name bname, o.buy_date buydate, o.total total, o.c_id cid "
				+ "from ORDERS o "
				+ "left join BOOKS b on o.b_id = b.id ) "
				+ "where cid = ?";
				
		
		List<PayBooksDTO> list = new ArrayList<PayBooksDTO>();
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			con=DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, custId);
			rs = pst.executeQuery();
			while (rs.next()) {
				String bookName = rs.getString("bname");
				int total = rs.getInt("total");
				Date buyTime = new java.sql.Date(rs.getDate("buydate").getTime());
				PayBooksDTO dto = new PayBooksDTO(bookName, total, buyTime);
				list.add(dto);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(null, pst, rs);
		}
		return list;
	}

	@Override
	public boolean addOrders(String custId, Map<String, PayBooksDTO> orders) {
		String sql = "insert into orders(id, b_id, c_id, total, buy_date) values(?, ?, ?, ?, ?)";
		Connection con=null;
		PreparedStatement pst = null;
		try {
			con=DBUtil.getConnection();
			for(String bId: orders.keySet()){
				pst = con.prepareStatement(sql);
				pst.setString(1, UUID.randomUUID().toString());
				pst.setString(2, bId);
				pst.setString(3, custId);
				pst.setInt(4, OrdersDB.getOrdersMap().get(bId).getTotal());
				pst.setDate(5, new java.sql.Date(OrdersDB.getOrdersMap().get(bId).getBuyTime().getTime()));
				pst.executeUpdate();
				
			}
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, pst, null);
		}
		return false;
	}
	
}
